<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>大余之家-高考Excel数据提取工具</title>
    <script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
    <script src="https://unpkg.com/file-saver/dist/FileSaver.min.js"></script>
    <style>
        * {
            box-sizing: border-box;
            font-family: 'Segoe UI', 'Microsoft YaHei', sans-serif;
        }
         
        body {
            background: linear-gradient(135deg, #f5f7fa 0%, #e4edf5 100%);
            min-height: 100vh;
            margin: 0;
            padding: 20px;
            color: #333;
        }
         
        .container {
            max-width: 1200px;
            margin: 0 auto;
            background: white;
            border-radius: 12px;
            box-shadow: 0 10px 30px rgba(0, 0, 0, 0.08);
            overflow: hidden;
        }
         
        header {
            background: linear-gradient(90deg, #1e5799 0%, #207cca 51%, #2989d8 100%);
            color: white;
            padding: 25px 40px;
            position: relative;
        }
         
        h1 {
            margin: 0;
            font-size: 28px;
            display: flex;
            align-items: center;
            gap: 15px;
        }
         
        h1 i {
            font-size: 32px;
        }
         
        .subtitle {
            margin-top: 8px;
            opacity: 0.9;
            font-weight: 300;
        }
         
        .app-content {
            padding: 25px 40px 40px;
        }
         
        .card {
            background: white;
            border-radius: 10px;
            box-shadow: 0 4px 15px rgba(0, 0, 0, 0.05);
            padding: 25px;
            margin-bottom: 30px;
        }
         
        .card-title {
            font-size: 18px;
            font-weight: 600;
            margin-top: 0;
            margin-bottom: 20px;
            color: #1e5799;
            display: flex;
            align-items: center;
            gap: 10px;
        }
         
        .file-input-group {
            display: flex;
            gap: 15px;
            margin-bottom: 15px;
            align-items: center;
        }
         
        .file-input-group input {
            flex: 1;
            padding: 12px 15px;
            border: 1px solid #ddd;
            border-radius: 6px;
            font-size: 14px;
        }
         
        .file-input-group button {
            background: #4a90e2;
            color: white;
            border: none;
            padding: 12px 20px;
            border-radius: 6px;
            cursor: pointer;
            font-weight: 500;
            transition: all 0.2s;
        }
         
        .file-input-group button:hover {
            background: #3a7bc8;
        }
         
        .listbox-container {
            display: flex;
            gap: 25px;
            margin-top: 15px;
        }
         
        .listbox-section {
            flex: 1;
        }
         
        .listbox-header {
            display: flex;
            justify-content: space-between;
            margin-bottom: 10px;
        }
         
        .listbox {
            border: 1px solid #ddd;
            border-radius: 6px;
            height: 180px;
            overflow-y: auto;
            padding: 10px;
            background: #f9fbfd;
        }
         
        .list-item {
            padding: 8px 12px;
            margin-bottom: 6px;
            background: #edf4ff;
            border-radius: 4px;
            cursor: pointer;
            transition: all 0.2s;
        }
         
        .list-item:hover {
            background: #d9e7ff;
        }
         
        .list-item.selected {
            background: #4a90e2;
            color: white;
        }
         
        .add-header {
            display: flex;
            gap: 10px;
            margin-top: 15px;
        }
         
        .add-header input {
            flex: 1;
            padding: 10px 15px;
            border: 1px solid #ddd;
            border-radius: 6px;
        }
         
        .add-header button {
            background: #5cb85c;
            color: white;
            border: none;
            padding: 10px 20px;
            border-radius: 6px;
            cursor: pointer;
        }
         
        .progress-container {
            margin: 25px 0;
            background: #f0f5ff;
            border-radius: 8px;
            padding: 15px;
        }
         
        .progress-bar {
            height: 12px;
            background: #e0e7f1;
            border-radius: 6px;
            overflow: hidden;
            margin-top: 8px;
        }
         
        .progress-fill {
            height: 100%;
            background: linear-gradient(90deg, #4a90e2 0%, #64b5f6 100%);
            border-radius: 6px;
            width: 0%;
            transition: width 0.3s ease;
        }
         
        .progress-text {
            text-align: center;
            font-size: 14px;
            color: #555;
            margin-top: 5px;
        }
         
        .button-group {
            display: flex;
            gap: 15px;
            justify-content: center;
            margin-top: 30px;
        }
         
        .btn {
            padding: 14px 32px;
            border: none;
            border-radius: 8px;
            font-size: 16px;
            font-weight: 600;
            cursor: pointer;
            transition: all 0.2s;
        }
         
        .btn-primary {
            background: linear-gradient(90deg, #1e5799 0%, #2989d8 100%);
            color: white;
            box-shadow: 0 4px 10px rgba(30, 87, 153, 0.3);
        }
         
        .btn-primary:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 15px rgba(30, 87, 153, 0.4);
        }
         
        .btn-secondary {
            background: #f0f5ff;
            color: #1e5799;
            border: 1px solid #4a90e2;
        }
         
        .status-bar {
            margin-top: 25px;
            padding: 15px;
            background: #f0f8ff;
            border-radius: 8px;
            text-align: center;
            font-size: 14px;
            color: #2989d8;
            border: 1px solid #c2e0ff;
        }
         
        .result-info {
            margin-top: 25px;
            padding: 20px;
            background: #e8f5e9;
            border-radius: 8px;
            border: 1px solid #c8e6c9;
            display: none;
        }
         
        .features {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
            gap: 20px;
            margin-top: 30px;
        }
         
        .feature-card {
            background: #f8fdff;
            border: 1px solid #e1f0fa;
            border-radius: 10px;
            padding: 20px;
            transition: transform 0.3s;
        }
         
        .feature-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 20px rgba(0, 0, 0, 0.05);
        }
         
        .feature-icon {
            font-size: 36px;
            color: #4a90e2;
            margin-bottom: 15px;
        }
         
        .feature-title {
            font-weight: 600;
            margin: 0 0 10px;
            color: #1e5799;
        }
         
        footer {
            text-align: center;
            padding: 25px;
            color: #7a8ca5;
            font-size: 14px;
            background: #f5f9ff;
            border-top: 1px solid #e2ebf7;
        }
         
        [url=home.php?mod=space&uid=945662]@media[/url] (max-width: 768px) {
            .listbox-container {
                flex-direction: column;
            }
             
            .file-input-group {
                flex-direction: column;
                align-items: stretch;
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <header>
            <h1>
                <i>&#128202;</i> 高考比对Excel数据提取工具
            </h1>
            <div class="subtitle">自动匹配并提取Excel数据 · 支持多工作表 · 保留数据类型 · 批量处理</div>
        </header>
         
        <div class="app-content">
            <div class="card">
                <h2 class="card-title"><i>&#128193;</i> 文件选择</h2>
                 
                <div class="file-input-group">
                    <input type="text" id="file1-path" placeholder="数据文件1 (将被修改的文件)" readonly>
                    <button id="browse-file1">浏览...</button>
                </div>
                 
                <div class="file-input-group">
                    <input type="text" id="file2-path" placeholder="数据文件2 (数据来源文件)" readonly>
                    <button id="browse-file2">浏览...</button>
                </div>
            </div>
             
            <div class="listbox-container">
                <div class="card">
                    <div class="listbox-header">
                        <h2 class="card-title"><i>&#128269;</i> 比较列设置</h2>
                    </div>
                    <div class="listbox" id="compare-listbox"></div>
                    <div class="add-header">
                        <input type="text" id="new-compare-header" placeholder="输入要添加的比较列">
                        <button id="add-compare-header">添加</button>
                    </div>
                </div>
                 
                <div class="card">
                    <div class="listbox-header">
                        <h2 class="card-title"><i>&#128229;</i> 提取列设置</h2>
                    </div>
                    <div class="listbox" id="extract-listbox"></div>
                    <div class="add-header">
                        <input type="text" id="new-extract-header" placeholder="输入要添加的提取列">
                        <button id="add-extract-header">添加</button>
                    </div>
                </div>
            </div>
             
            <div class="card">
                <h2 class="card-title"><i>&#9881;&#65039;</i> 高级设置</h2>
                 
                <div style="display: flex; gap: 20px; margin-bottom: 20px;">
                    <div style="flex: 1;">
                        <label>文件1工作表:</label>
                        <select id="sheet1-select" style="width: 100%; padding: 10px; border-radius: 6px; border: 1px solid #ddd;">
                            <option value="">自动选择活动工作表</option>
                        </select>
                    </div>
                     
                    <div style="flex: 1;">
                        <label>文件2工作表:</label>
                        <select id="sheet2-select" style="width: 100%; padding: 10px; border-radius: 6px; border: 1px solid #ddd;">
                            <option value="">自动选择活动工作表</option>
                        </select>
                    </div>
                </div>
                 
                <div>
                    <label>
                        <input type="checkbox" id="batch-mode" style="margin-right: 8px;">
                        批量处理模式（处理文件夹中的所有Excel文件）
                    </label>
                </div>
            </div>
             
            <div class="progress-container">
                <div>处理进度:</div>
                <div class="progress-bar">
                    <div class="progress-fill" id="progress-fill"></div>
                </div>
                <div class="progress-text" id="progress-text">0%</div>
            </div>
             
            <div class="button-group">
                <button class="btn btn-primary" id="process-btn">开始提取</button>
                <button class="btn btn-secondary" id="clear-btn">清除所有</button>
            </div>
             
            <div class="status-bar" id="status-bar">
                就绪
            </div>
             
            <div class="result-info" id="result-info">
                <strong>处理完成！</strong> 共找到 <span id="match-count">0</span> 条匹配记录。
                结果文件已保存，请点击下方按钮下载：
                <div style="margin-top: 15px; text-align: center;">
                    <button class="btn btn-primary" id="download-btn">下载结果文件</button>
                </div>
            </div>
             
            <div class="features">
                <div class="feature-card">
                    <div class="feature-icon">&#128269;</div>
                    <h3 class="feature-title">智能匹配</h3>
                    <p>通过比较列自动匹配不同Excel文件中的数据，行列顺序可不同</p>
                </div>
                 
                <div class="feature-card">
                    <div class="feature-icon">&#128202;</div>
                    <h3 class="feature-title">多工作表支持</h3>
                    <p>支持选择特定工作表进行处理，满足复杂Excel文件需求</p>
                </div>
                 
                <div class="feature-card">
                    <div class="feature-icon">&#9889;</div>
                    <h3 class="feature-title">批量处理</h3>
                    <p>可处理整个文件夹中的Excel文件，大幅提升工作效率</p>
                </div>
            </div>
        </div>
         
        <footer>
            <p>Excel数据提取工具 &#169; 2025 | 高考志愿填报神器 | 数据智能处理专家</p>
        </footer>
    </div>
     
    <script>
        // 全局变量
        let file1 = null;
        let file2 = null;
        let file1Workbook = null;
        let file2Workbook = null;
        let resultWorkbook = null;
        let compareHeaders = [];
        let extractHeaders = [];
         
        // DOM元素
        const file1Path = document.getElementById('file1-path');
        const file2Path = document.getElementById('file2-path');
        const browseFile1Btn = document.getElementById('browse-file1');
        const browseFile2Btn = document.getElementById('browse-file2');
        const compareListbox = document.getElementById('compare-listbox');
        const extractListbox = document.getElementById('extract-listbox');
        const newCompareHeader = document.getElementById('new-compare-header');
        const newExtractHeader = document.getElementById('new-extract-header');
        const addCompareHeaderBtn = document.getElementById('add-compare-header');
        const addExtractHeaderBtn = document.getElementById('add-extract-header');
        const processBtn = document.getElementById('process-btn');
        const clearBtn = document.getElementById('clear-btn');
        const progressFill = document.getElementById('progress-fill');
        const progressText = document.getElementById('progress-text');
        const statusBar = document.getElementById('status-bar');
        const resultInfo = document.getElementById('result-info');
        const matchCount = document.getElementById('match-count');
        const downloadBtn = document.getElementById('download-btn');
        const sheet1Select = document.getElementById('sheet1-select');
        const sheet2Select = document.getElementById('sheet2-select');
         
        // 事件监听器
        browseFile1Btn.addEventListener('click', () => handleFileSelect('file1'));
        browseFile2Btn.addEventListener('click', () => handleFileSelect('file2'));
        addCompareHeaderBtn.addEventListener('click', () => addHeader('compare'));
        addExtractHeaderBtn.addEventListener('click', () => addHeader('extract'));
        processBtn.addEventListener('click', processFiles);
        clearBtn.addEventListener('click', clearAll);
        downloadBtn.addEventListener('click', downloadResult);
         
        // 文件选择处理
        function handleFileSelect(type) {
            const input = document.createElement('input');
            input.type = 'file';
            input.accept = '.xlsx,.xls';
             
            input.onchange = e => {
                const file = e.target.files[0];
                if (!file) return;
                 
                const reader = new FileReader();
                reader.onload = function(e) {
                    try {
                        const data = new Uint8Array(e.target.result);
                        const workbook = XLSX.read(data, {type: 'array'});
                         
                        if (type === 'file1') {
                            file1 = file;
                            file1Path.value = file.name;
                            file1Workbook = workbook;
                            populateSheetSelect(sheet1Select, workbook);
                        } else {
                            file2 = file;
                            file2Path.value = file.name;
                            file2Workbook = workbook;
                            populateSheetSelect(sheet2Select, workbook);
                        }
                         
                        statusBar.textContent = `已加载文件: ${file.name}`;
                    } catch (error) {
                        alert(`加载文件失败: ${error.message}`);
                        statusBar.textContent = "文件加载失败";
                    }
                };
                 
                reader.readAsArrayBuffer(file);
            };
             
            input.click();
        }
         
        // 填充工作表选择下拉框
        function populateSheetSelect(selectElement, workbook) {
            // 清空现有选项（保留第一个选项）
            while (selectElement.options.length > 1) {
                selectElement.remove(1);
            }
             
            // 添加新的工作表选项
            workbook.SheetNames.forEach(sheetName => {
                const option = document.createElement('option');
                option.value = sheetName;
                option.textContent = sheetName;
                selectElement.appendChild(option);
            });
        }
         
        // 添加列头
        function addHeader(type) {
            const input = type === 'compare' ? newCompareHeader : newExtractHeader;
            const listbox = type === 'compare' ? compareListbox : extractListbox;
            const headers = type === 'compare' ? compareHeaders : extractHeaders;
             
            const header = input.value.trim();
            if (!header) {
                alert("请输入列名");
                return;
            }
             
            if (headers.includes(header)) {
                alert("该列已添加");
                return;
            }
             
            headers.push(header);
            renderListbox(listbox, headers);
            input.value = "";
        }
         
        // 渲染列表框
        function renderListbox(listbox, items) {
            listbox.innerHTML = "";
            items.forEach(item => {
                const div = document.createElement('div');
                div.className = "list-item";
                div.textContent = item;
                 
                // 添加点击删除功能
                div.addEventListener('click', function() {
                    const index = items.indexOf(item);
                    if (index > -1) {
                        items.splice(index, 1);
                        renderListbox(listbox, items);
                    }
                });
                 
                listbox.appendChild(div);
            });
        }
         
        // 更新进度条
        function updateProgress(percent, message) {
            progressFill.style.width = `${percent}%`;
            progressText.textContent = `${Math.round(percent)}% - ${message}`;
            statusBar.textContent = message;
        }
         
        // 处理文件
        function processFiles() {
            // 验证输入
            if (!file1Workbook || !file2Workbook) {
                alert("请选择两个Excel文件");
                return;
            }
             
            if (compareHeaders.length === 0) {
                alert("至少需要指定一个比较列");
                return;
            }
             
            if (extractHeaders.length === 0) {
                alert("请指定要提取的列");
                return;
            }
             
            // 获取选择的工作表
            const sheet1Name = sheet1Select.value || file1Workbook.SheetNames[0];
            const sheet2Name = sheet2Select.value || file2Workbook.SheetNames[0];
             
            try {
                updateProgress(0, "开始处理...");
                 
                // 获取工作表
                const ws1 = file1Workbook.Sheets[sheet1Name];
                const ws2 = file2Workbook.Sheets[sheet2Name];
                 
                if (!ws1 || !ws2) {
                    alert("无法找到指定的工作表");
                    return;
                }
                 
                // 转换为JSON
                updateProgress(10, "解析文件1...");
                const data1 = XLSX.utils.sheet_to_json(ws1, {header: 1});
                const headers1 = data1[0];
                 
                updateProgress(20, "解析文件2...");
                const data2 = XLSX.utils.sheet_to_json(ws2, {header: 1});
                const headers2 = data2[0];
                 
                // 检查列是否存在
                for (const header of compareHeaders) {
                    if (!headers1.includes(header)) {
                        alert(`文件1中不存在列: ${header}`);
                        return;
                    }
                    if (!headers2.includes(header)) {
                        alert(`文件2中不存在列: ${header}`);
                        return;
                    }
                }
                 
                for (const header of extractHeaders) {
                    if (!headers2.includes(header)) {
                        alert(`文件2中不存在列: ${header}`);
                        return;
                    }
                }
                 
                // 构建文件2的索引
                updateProgress(30, "构建索引...");
                const file2Index = {};
                const compareHeaderIndexes = compareHeaders.map(h => headers2.indexOf(h));
                const extractHeaderIndexes = extractHeaders.map(h => headers2.indexOf(h));
                 
                for (let i = 1; i < data2.length; i++) {
                    const row = data2[i];
                    const keyParts = compareHeaderIndexes.map(idx => row[idx]);
                    const key = JSON.stringify(keyParts);
                     
                    // 存储提取列的数据
                    const rowData = {};
                    extractHeaderIndexes.forEach((idx, j) => {
                        rowData[extractHeaders[j]] = row[idx];
                    });
                     
                    file2Index[key] = rowData;
                     
                    // 更新进度
                    if (i % 100 === 0) {
                        const progress = 30 + Math.floor(i / data2.length * 30);
                        updateProgress(progress, `索引构建中: ${i}/${data2.length-1} 行`);
                    }
                }
                 
                // 处理文件1
                updateProgress(60, "匹配数据...");
                let matches = 0;
                const resultData = [headers1.slice()]; // 复制表头
                 
                // 添加缺失的提取列
                for (const header of extractHeaders) {
                    if (!headers1.includes(header)) {
                        resultData[0].push(header);
                    }
                }
                 
                const compareHeaderIndexes1 = compareHeaders.map(h => headers1.indexOf(h));
                 
                for (let i = 1; i < data1.length; i++) {
                    const row = data1[i].slice(); // 复制行
                    const keyParts = compareHeaderIndexes1.map(idx => row[idx]);
                    const key = JSON.stringify(keyParts);
                     
                    if (file2Index[key]) {
                        matches++;
                        const rowData = file2Index[key];
                         
                        // 添加提取的数据
                        for (const header of extractHeaders) {
                            const value = rowData[header];
                            const headerIndex = headers1.indexOf(header);
                             
                            if (headerIndex === -1) {
                                // 添加新列
                                row.push(value);
                            } else {
                                // 更新现有列
                                row[headerIndex] = value;
                            }
                        }
                    }
                     
                    resultData.push(row);
                     
                    // 更新进度
                    if (i % 100 === 0) {
                        const progress = 60 + Math.floor(i / data1.length * 35);
                        updateProgress(progress, `匹配数据中: ${i}/${data1.length-1} 行`);
                    }
                }
                 
                // 创建结果工作表
                updateProgress(95, "生成结果文件...");
                resultWorkbook = XLSX.utils.book_new();
                const resultWs = XLSX.utils.aoa_to_sheet(resultData);
                XLSX.utils.book_append_sheet(resultWorkbook, resultWs, "提取结果");
                 
                // 显示结果
                matchCount.textContent = matches;
                resultInfo.style.display = "block";
                updateProgress(100, `处理完成! 找到 ${matches} 条匹配记录`);
            } catch (error) {
                alert(`处理过程中发生错误: ${error.message}`);
                updateProgress(0, "处理失败");
                console.error(error);
            }
        }
         
        // 下载结果
        function downloadResult() {
            if (!resultWorkbook) {
                alert("没有可下载的结果");
                return;
            }
             
            const fileName = file1.name.replace('.xlsx', '') + '_提取结果.xlsx';
            XLSX.writeFile(resultWorkbook, fileName);
        }
         
        // 清除所有
        function clearAll() {
            file1 = null;
            file2 = null;
            file1Workbook = null;
            file2Workbook = null;
            resultWorkbook = null;
            compareHeaders = [];
            extractHeaders = [];
             
            file1Path.value = "";
            file2Path.value = "";
            newCompareHeader.value = "";
            newExtractHeader.value = "";
            compareListbox.innerHTML = "";
            extractListbox.innerHTML = "";
            progressFill.style.width = "0%";
            progressText.textContent = "0%";
            statusBar.textContent = "已清除所有设置";
            resultInfo.style.display = "none";
             
            // 重置工作表选择
            sheet1Select.innerHTML = '<option value="">自动选择活动工作表</option>';
            sheet2Select.innerHTML = '<option value="">自动选择活动工作表</option>';
        }
    </script>
</body>
</html>